﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PM.Entity;
using System.Data;
using System.Data.OracleClient;

namespace PM.Services
{
    public static class DividendServices
    {
        private static Dividend GetDividendFromDataReader(IDataReader dataReader)
        {
            Dividend retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Dividend();
                retVal.CompanyId = dataReader["COMPANY_ID"].ToString();
                retVal.Name = dataReader["COMPANY_NAME"].ToString();
                DateTime announcedDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["ANNOUNCED"].ToString(), out announcedDate))
                {
                    retVal.AnnouncedDate = announcedDate;
                }
                decimal rateOfDividend = 0;
                if (decimal.TryParse(dataReader["RATE_OF_DIVIDEND"].ToString(), out rateOfDividend))
                {
                    retVal.RateOfDividend = rateOfDividend;
                }
                retVal.DividendType = dataReader["DIVIDEND_TYPE"].ToString();
                retVal.FinancialYear = dataReader["FINANCIAL_YEAR"].ToString();
                DateTime xd = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["XD"].ToString(), out xd))
                {
                    retVal.XD = xd;
                }
                DateTime paymentDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["PAYMENT"].ToString(), out paymentDate))
                {
                    retVal.Payment = paymentDate;
                }
                retVal.Remarks = dataReader["REMARKS"].ToString();

                decimal propotion = 0;
                if (decimal.TryParse(dataReader["PROPOTION"].ToString(), out propotion))
                {
                    retVal.Propotion = propotion;
                }
                Int64 noOfNewShares = 0;
                if (Int64.TryParse(dataReader["NO_OF_NEW_SHARES"].ToString(), out noOfNewShares))
                {
                    retVal.NoOfNewSharesListed = noOfNewShares;
                }
                decimal valuePerShare = 0;
                if (decimal.TryParse(dataReader["VALUE_PER_SHARE"].ToString(), out valuePerShare))
                {
                    retVal.ValuePerShare = valuePerShare;
                }

                decimal tax = 0;
                if (decimal.TryParse(dataReader["TAX_RATE"].ToString(), out tax))
                {
                    retVal.TaxRate = tax;
                }
            }
            return retVal;
        }

        private static DividendReceivable GetDividendReceivableFromDataReader(IDataReader dataReader, bool viewOnly)
        {
            DividendReceivable retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DividendReceivable();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                DateTime xd = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["XD"].ToString(), out xd))
                {
                    retVal.XD = xd;
                }
                decimal rateOfDividend = 0;
                if (decimal.TryParse(dataReader["RATE_OF_DIVIDEND"].ToString(), out rateOfDividend))
                {
                    retVal.RateOfDividend = rateOfDividend;
                }
                int quantity = 0;
                int.TryParse(dataReader["QUANTITY"].ToString(), out quantity);
                retVal.Quantity = quantity;

                decimal amount = 0;
                if (decimal.TryParse(dataReader["AMOUNT"].ToString(), out amount))
                {
                    retVal.Amount = amount;
                }

                decimal tax = 0;
                if (decimal.TryParse(dataReader["TAX_RATE"].ToString(), out tax))
                {
                    retVal.TaxRate = tax;
                }

                if (!viewOnly)
                {
                    int received = 0;
                    int.TryParse(dataReader["RECEIVED"].ToString(), out received);
                    if (received == 1)
                    {
                        retVal.Received = true;
                    }
                    else
                    {
                        retVal.Received = false;
                    }

                    retVal.Remarks = dataReader["REMARKS"].ToString();

                    DateTime payment = DateTime.MinValue;
                    if (DateTime.TryParse(dataReader["PAYMENT"].ToString(), out payment))
                    {
                        retVal.PaymentDate = payment;
                    }
                }


            }
            return retVal;
        }

        private static DividendReceivable GetScripReceivableFromDataReader(IDataReader dataReader)
        {
            DividendReceivable retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DividendReceivable();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                DateTime xd = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["XD"].ToString(), out xd))
                {
                    retVal.XD = xd;
                }
                decimal propotion = 0;
                decimal.TryParse(dataReader["PROPOTION"].ToString(), out propotion);
                retVal.Propotion = propotion;

                int quantity = 0;
                int.TryParse(dataReader["QUANTITY"].ToString(), out quantity);
                retVal.Quantity = quantity;

                decimal quantityReceivable = 0;
                decimal.TryParse(dataReader["AMOUNT"].ToString(), out quantityReceivable);
                retVal.QuantityReceivable = (int)quantityReceivable;

                DateTime payment = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["PAYMENT"].ToString(), out payment))
                {
                    retVal.PaymentDate = payment;
                }

            }
            return retVal;
        }

        private static DividendReceivable Get_Bonus_Receivable_From_DataReader(IDataReader dataReader)
        {
            DividendReceivable retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DividendReceivable();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                DateTime xd = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["XD"].ToString(), out xd))
                {
                    retVal.XD = xd;
                }
                decimal propotion = 0;
                decimal.TryParse(dataReader["PROPOTION"].ToString(), out propotion);
                retVal.Propotion = propotion;

                int quantity = 0;
                int.TryParse(dataReader["QUANTITY"].ToString(), out quantity);
                retVal.Quantity = quantity;

                decimal quantityReceivable = 0;
                decimal.TryParse(dataReader["QTY_RECEIVABLE"].ToString(), out quantityReceivable);
                retVal.QuantityReceivable = (int)quantityReceivable;

                decimal issueValue = 0;
                decimal.TryParse(dataReader["VALUE_PER_SHARE"].ToString(), out issueValue);
                retVal.ValuePerShare = issueValue;

                decimal amount = 0;
                decimal.TryParse(dataReader["AMOUNT"].ToString(), out amount);
                retVal.Amount = amount;

                DateTime payment = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["PAYMENT"].ToString(), out payment))
                {
                    retVal.PaymentDate = payment;
                }

            }
            return retVal;
        }

        private static DividendReceivable Get_Rights_Receivable_From_DataReader(IDataReader dataReader)
        {
            DividendReceivable retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new DividendReceivable();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                DateTime xd = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["XD"].ToString(), out xd))
                {
                    retVal.XD = xd;
                }
                decimal propotion = 0;
                decimal.TryParse(dataReader["PROPOTION"].ToString(), out propotion);
                retVal.Propotion = propotion;

                int quantity = 0;
                int.TryParse(dataReader["QUANTITY"].ToString(), out quantity);
                retVal.Quantity = quantity;

                decimal quantityReceivable = 0;
                decimal.TryParse(dataReader["QTY_RECEIVABLE"].ToString(), out quantityReceivable);
                retVal.QuantityReceivable = (int)quantityReceivable;

                decimal issueValue = 0;
                decimal.TryParse(dataReader["VALUE_PER_SHARE"].ToString(), out issueValue);
                retVal.ValuePerShare = issueValue;

                decimal amount = 0;
                decimal.TryParse(dataReader["AMOUNT"].ToString(), out amount);
                retVal.Amount = amount;

                DateTime payment = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["PAYMENT"].ToString(), out payment))
                {
                    retVal.PaymentDate = payment;
                }

                DateTime commencement = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["COMMENCEMENT_DATE"].ToString(), out commencement))
                {
                    retVal.TradeCommencementDate = commencement;
                }

                DateTime renunciation = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["RENUNCIATION_DATE"].ToString(), out renunciation))
                {
                    retVal.RenunciationDate = renunciation;
                }

            }
            return retVal;
        }

        public static Dividend GetDividendByIDs(string company_id, DateTime announcedDate)
        {
            Dividend retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where COMPANY_ID = '" + company_id + "' and ANNOUNCED = '" + announcedDate + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetDividendFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetAllDividendCollection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND ";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetFutureDividendAnnouncementCollection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where RATE_OF_DIVIDEND >=0 and (PAYMENT +4) >= SYSDATE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetFutureDividendAnnouncement_HomePage_Collection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where RATE_OF_DIVIDEND >=0 and (XD) = trunc(SYSDATE)";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetFutureScripDividendAnnounceCollection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where PROPOTION >=0 and VALUE_PER_SHARE = -1 and (PAYMENT +4) >= SYSDATE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetFutureBonusIssueAnnounceCollection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where VALUE_PER_SHARE >=0 and IS_RIGHTS_ISSUE = 0 and (PAYMENT +4) >= SYSDATE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendCollection GetFutureRightsIssueAnnounceCollection()
        {
            DividendCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from DIVIDEND where IS_RIGHTS_ISSUE = 1 and (PAYMENT +4) >= SYSDATE";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendCollection();
                    while (dataReader.Read())
                    {
                        Dividend item = GetDividendFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetFutureDividendReceivableCollection()
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Dividend_Future_Receiv_View";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetDividendReceivableFromDataReader(dataReader, true);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetFutureDividReceivCollByClientId(string clientId)
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Dividend_Future_Receiv_View where CLIENT_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetDividendReceivableFromDataReader(dataReader, true);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetDividendReceivableCollection()
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Dividend_Receivable_View";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetDividendReceivableFromDataReader(dataReader, false);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetDividendReceivCollByClientId(string clientId)
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Dividend_Receivable_View where CLIENT_ID = :v1 ";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetDividendReceivableFromDataReader(dataReader, false);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetScripDividendReceivableCollection()
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Scrip_Future_Receive_View";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetScripReceivableFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection GetScripDividendReceivableCollectionByClientId(string clientId)
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Scrip_Future_Receive_View where Client_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = GetScripReceivableFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection Get_Bonus_Issue_Receivable_Collection()
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Bouns_Future_Receive_View";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = Get_Bonus_Receivable_From_DataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection Get_Bonus_Receivable_Collection_By_ClientId(string clientId)
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Bouns_Future_Receive_View where Client_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = Get_Bonus_Receivable_From_DataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection Get_Rights_Issue_Receivable_Collection()
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Rights_Future_Receive_View";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = Get_Rights_Receivable_From_DataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DividendReceivableCollection Get_Rights_Receivable_Collection_By_ClientId(string clientId)
        {
            DividendReceivableCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Rights_Future_Receive_View where Client_ID = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = clientId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new DividendReceivableCollection();
                    while (dataReader.Read())
                    {
                        DividendReceivable item = Get_Rights_Receivable_From_DataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        /* 
         * Not complete
         * public static DividendReceivable GetDividendReceivableByIDs(string client_id, string security_id, DateTime xd)
         {
             Dividend retVal = null;
             OracleConnection con = new OracleConnection();

             try
             {
                 con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                 con.Open();
                 OracleCommand cmd = new OracleCommand();
                 cmd.CommandText = "select * from DIVIDEND where COMPANY_ID = '" + company_id + "' and ANNOUNCED = '" + announcedDate + "'";
                 cmd.Connection = con;

                 using (IDataReader dataReader = cmd.ExecuteReader())
                 {
                     if (dataReader.Read())
                     {
                         retVal = GetDividendFromDataReader(dataReader);
                     }
                 }
             }
             catch (System.Exception exc)
             {
                 throw;
             }
             finally
             {
                 con.Close();
             }
             return retVal;
         }
         */
        public static void Add(Dividend dividend)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "insert into DIVIDEND(COMPANY_ID, COMPANY_NAME, ANNOUNCED, RATE_OF_DIVIDEND, DIVIDEND_TYPE," +
                                                       "FINANCIAL_YEAR, XD, PAYMENT, REMARKS, PROPOTION, NO_OF_NEW_SHARES," +
                                                       "VALUE_PER_SHARE, RENUNCIATION_DATE, COMMENCEMENT_DATE, IS_RIGHTS_ISSUE, TAX_RATE)";


                cmd.CommandText += "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15,:v16)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dividend.CompanyId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dividend.Name;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dividend.AnnouncedDate;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dividend.RateOfDividend;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = dividend.DividendType;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = dividend.FinancialYear;
                cmd.Parameters.Add(":v7", OracleType.DateTime).Value = dividend.XD;
                cmd.Parameters.Add(":v8", OracleType.DateTime).Value = dividend.Payment;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = dividend.Remarks;
                /*Scrip*/
                cmd.Parameters.Add(":v10", OracleType.Number).Value = dividend.Propotion;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = dividend.NoOfNewSharesListed;
                /*Bonus*/
                cmd.Parameters.Add(":v12", OracleType.Number).Value = dividend.ValuePerShare;
                /*Right*/
                cmd.Parameters.Add(":v13", OracleType.DateTime).Value = dividend.RenunciationDate;
                cmd.Parameters.Add(":v14", OracleType.DateTime).Value = dividend.TradeCommencementDate;
                if (dividend.IsRightsIssue)
                {
                    cmd.Parameters.Add(":v15", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v15", OracleType.Number).Value = 0;
                }
                cmd.Parameters.Add(":v16", OracleType.Number).Value = dividend.TaxRate;


                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(DividendReceivable dividendReceivable)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select 1 from CLIENT_DIVIDENDS where CLIENT_ID = :v1 " +
                                                            "and SECURITY_ID = :v2 and  XD = :v3";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dividendReceivable.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dividendReceivable.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dividendReceivable.XD;

                cmd.Connection = con;
                object obj = cmd.ExecuteScalar();
                if (obj != null && int.Parse(obj.ToString()) == 1)
                {
                    return;
                }

                cmd.Parameters.Clear();
                cmd.CommandText = string.Empty;
                cmd.CommandText = "insert into CLIENT_DIVIDENDS(CLIENT_ID, SECURITY_ID, XD, QUANTITY, RATE_OF_DIVIDEND, AMOUNT," +
                                                               "RECEIVED, REMARKS)";


                cmd.CommandText += "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dividendReceivable.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dividendReceivable.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dividendReceivable.XD;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dividendReceivable.Quantity;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = dividendReceivable.RateOfDividend;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = dividendReceivable.Amount;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = dividendReceivable.Received;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = dividendReceivable.Remarks;

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void AddDividendReceivables()
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.Parameters.Clear();
                cmd.CommandText = string.Empty;
                cmd.CommandText = "insert into CLIENT_DIVIDENDS(CLIENT_ID, SECURITY_ID, XD, QUANTITY, RATE_OF_DIVIDEND," +
                                                               "AMOUNT, RECEIVED, REMARKS, TAX_RATE) " +
                                  "select client_id, security_id, xd, quantity, rate_of_dividend, " +
                                          "Amount, 0, null, tax_rate " +
                                  " from Dividend_Future_Receiv_View " +
                                  " where xd = trunc(sysdate)";

                cmd.Connection = con;

                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void UpdateDividendReceivables(DividendReceivable dr)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update CLIENT_DIVIDENDS set " +
                                  "RECEIVED = :v4, " +
                                  "REMARKS = :v5";


                cmd.CommandText += " where CLIENT_ID = :v1 and SECURITY_ID = :v2 and XD= :v3";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dr.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = dr.SecurityId;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = dr.XD;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dr.Received;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = dr.Remarks;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


        public static void Delete(string company_id, DateTime xd)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from DIVIDEND where COMPANY_ID = '" + company_id + "' and XD = '" + xd + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Dividend dividend)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();

                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update DIVIDEND set " +
                                  "COMPANY_NAME = :v3, " +
                                  "RATE_OF_DIVIDEND = :v4, " +
                                  "DIVIDEND_TYPE = :v5, " +
                                  "FINANCIAL_YEAR = :v6, " +
                                  "ANNOUNCED  = :v7, " +
                                  "PAYMENT = :v8, " +
                                  "REMARKS = :v9, " +
                                  "PROPOTION = :v10, " +
                                  "NO_OF_NEW_SHARES = :v11, " +
                                  "VALUE_PER_SHARE = :v12, " +
                                  "RENUNCIATION_DATE = :v13, " +
                                  "COMMENCEMENT_DATE = :v14, "+
                                  "TAX_RATE = :v15";

                cmd.CommandText += " where COMPANY_ID = :v1 and  XD= :v2";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = dividend.CompanyId;
                cmd.Parameters.Add(":v2", OracleType.DateTime).Value = dividend.XD;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = dividend.Name;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = dividend.RateOfDividend;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = dividend.DividendType;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = dividend.FinancialYear;
                cmd.Parameters.Add(":v7", OracleType.DateTime).Value = dividend.AnnouncedDate;
                cmd.Parameters.Add(":v8", OracleType.DateTime).Value = dividend.Payment;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = dividend.Remarks;
                /*Scrip*/
                cmd.Parameters.Add(":v10", OracleType.Number).Value = dividend.Propotion;
                cmd.Parameters.Add(":v11", OracleType.Number).Value = dividend.NoOfNewSharesListed;
                /*Bonus*/
                cmd.Parameters.Add(":v12", OracleType.Number).Value = dividend.ValuePerShare;
                /*Right*/
                cmd.Parameters.Add(":v13", OracleType.DateTime).Value = dividend.RenunciationDate;
                cmd.Parameters.Add(":v14", OracleType.DateTime).Value = dividend.TradeCommencementDate;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = dividend.TaxRate;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }


    }
}
